using System;
using System.Collections.Generic;
using System.Text;

namespace SchemaProvider
{
    public static class OracleCommandUtility
    {
        //public static string ListTableNames()
        //{
        //    return "SELECT TABLE_NAME TableName FROM USER_TABLES";
        //}

        public static string ListTableNames()
        {
            return "SELECT decode(a.owner,null,u.table_name,a.owner || '.' || u.table_name) TableName FROM USER_TABLES u " +
                " LEFT JOIN ALL_TABLES a ON u.table_name=a.table_name AND u.tablespace_name=a.tablespace_name "+
                " ORDER BY a.owner,u.table_name ";
        }

        //public static string ListViewNames()
        //{
        //    return "SELECT VIEW_NAME TableName FROM USER_VIEWS";
        //}

        public static string ListViewNames()
        {
            return "SELECT decode(a.owner,null,u.view_name,a.owner || '.' || u.view_name) TableName FROM USER_VIEWS u " +
                " LEFT JOIN ALL_VIEWS a ON u.view_name=a.view_name " +
                " ORDER BY a.owner,u.view_name "; 
        }

        public static string ListPrimaryKeys()
        {
            return "select col.table_name TableName,col.column_name PrimaryKey from user_constraints con,  user_cons_columns col " +
                "where con.constraint_name = col.constraint_name and con.constraint_type='P'";
        }

        public static string ListForeignKey()
        {
            return "select con.table_name TableName,col.column_name ForeignKey,r.table_name ReferenceTableName,r.column_name ReferenceKey from " +
                "user_constraints con,user_cons_columns col, (select t2.table_name,t2.column_name,t1.r_constraint_name " +
                "from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name) r " +
                "where con.constraint_name=col.constraint_name and con.r_constraint_name=r.r_constraint_name";
        }

        //public static string ListProcedures()
        //{
        //    return "select t.object_name ProcedureName,t.argument_name ParameterName,t.data_length Length,t.in_out InOut,t.data_type ParameterType " +
        //            "from user_arguments t " +
        //            "where t.object_name in (select object_name from user_procedures)  order by t.object_name ";
        //}

        public static string ListProcedures()
        {
            return "select decode(t.package_name,null,t.object_name,t.package_name || '.' || t.object_name) || t.overload ProcedureName, "+
                " t.argument_name ParameterName,t.data_length Length,t.in_out InOut,t.data_type ParameterType " +
                    "from user_arguments t " +
                    " order by t.package_name,t.object_name ";
        }
    }
}
